﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class frmDanhmucGV : Form
    {
        bool temp;       //tùy chọn insert or update
        int count;      //Số lượng giảng viên
        int index;      //ví trí của giảng viên đó trong dữ liệu
        string makhoa;    //lấy mã khoa khi tùy chọn lkuMakhoa --> update or insert dữ liệu
        string matrinhdo;    //lấy mã trình độ khi tùy chọn lkuMaTD --> update or insert dữ liệu
        string phai;
        string dcgd;
        bool xoa;
        //string MagvID;
        SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SM;Integrated Security=True");
        string sql = "select * from T02_Giao_vien gv, T06_Trinh_do td, T16_Khoa kh where kh.Makhoa = gv.MaKhoa and td.MatrinhdoID = gv.MatrinhdoID";
        public frmDanhmucGV()
        {
            InitializeComponent();
            //Mã khoa
            string get_lkuMakhoa = "select * from T16_Khoa";
            DataSet ds1 = FillDataset(get_lkuMakhoa);
            lkuMakhoa.Properties.DataSource = ds1.Tables[0];
            lkuMakhoa.Properties.DisplayMember = "Makhoa";
            lkuMakhoa.Properties.ValueMember = "Makhoa";
            //Trình độ
            string get_lkuMaTD = "select * from T06_Trinh_do";
            DataSet ds2 = FillDataset(get_lkuMaTD);
            lkuMaTD.Properties.DataSource = ds2.Tables[0];
            lkuMaTD.Properties.DisplayMember = "MatrinhdoID";
            lkuMaTD.Properties.ValueMember = "MatrinhdoID";
            disable_textbox();
        }

        DataSet FillDataset(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter adap = new SqlDataAdapter(sql, connection);
                adap.Fill(ds);
                adap.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" + ex.Message);
            }
            return ds;
        }

        //get gridview
        public void get_gridviewdanhsachgv()
        {
            DataSet ds = FillDataset("select * from T02_Giao_vien");
            gCol_MaGV.FieldName = "MagvID";
            gCol_Holot.FieldName = "Hoten";
            gCol_Noisinh.FieldName = "NoiSinh";
            gCol_Diachi.FieldName = "Diachi";
            gridControl1.DataSource = ds.Tables[0];
        }

        //get DS Giảng viên
        public void get_Tabdanhsachgv(string sql, DataRow row)
        {
            reset_lookupedit();
            lblID.Text = row["ID"].ToString();
            txtMSGV.Text = row["MagvID"].ToString();
            txtHolot.Text = row["Hoten"].ToString();
           // lkuMakhoa.Properties.NullText = row["MaKhoa"].ToString();
            lkuMakhoa.Text = row["MaKhoa"].ToString();
            lkuMakhoa.ClosePopup();
            string temp = row["Ngaysinh"].ToString();
            if (temp == "")
            {
                dNgaysinh.EditValue = "";
            }
            else
            {
                DateTime ngaysinh = DateTime.Parse(temp);
                dNgaysinh.EditValue = ngaysinh.ToShortDateString();
            }
            txtNoiSinh.Text = row["Noisinh"].ToString();
            phai = row["Phai"].ToString();
            if (phai == "True")
                rgPhai.SelectedIndex = 0;
            else
                rgPhai.SelectedIndex = 1;
            txtSoCMND.Text = row["SoCMND"].ToString();
            txtDiachi.Text = row["Diachi"].ToString();
            txtDienthoai.Text = row["Dienthoai"].ToString();
            //lkuMaTD.Properties.NullText = row["MatrinhdoID"].ToString();
            lkuMaTD.Text = row["MatrinhdoID"].ToString();
            lkuMaTD.ClosePopup();
            cbChucdanh.Text = row["HocHam"].ToString();
            cbLoaiGV.Text = row["LoaiGV"].ToString();
            dcgd = row["TinhtrangGD"].ToString();
            if (dcgd == "True")
                ckDacogd.Checked = true;
            else
                ckDacogd.Checked = false;
            mGhichu.Text = row["Ghichu"].ToString();
            string temp1 = row["Ngayvetruong"].ToString();
            if (temp1 == "")
            {
                dNgayvetruong.EditValue = "";
            }
            else
            {
                DateTime ngayvetruong = DateTime.Parse(temp1);
                dNgaysinh.EditValue = ngayvetruong.ToShortDateString();
            }
        }

        public void reset_lookupedit()
        {
            lkuMakhoa.Refresh();
            lkuMaTD.Refresh();
        }

        private void frmDanhmucGV_Load(object sender, EventArgs e)
        {

            get_gridviewdanhsachgv();
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[0];
            count = ds.Tables[0].Rows.Count;
            get_Tabdanhsachgv(sql, row);
        }


        private void btnVedau_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[0];
            get_Tabdanhsachgv(sql, row);
            index = ds.Tables[0].Rows.IndexOf(row);

        }

        private void btnLui_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            index--;
            if (index >= 0)
            {
                DataSet ds = FillDataset(sql);
                DataRow row = ds.Tables[0].Rows[index];
                get_Tabdanhsachgv(sql, row);
                index = ds.Tables[0].Rows.IndexOf(row);
            }
            else
            {
                index++;
                if (xoa == false)
                    MessageBox.Show("Bạn đang ở mẫu tin đầu tiên");
                else
                    btnVedau_Click(sender, e);
            }
        }

        private void bntToi_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            index++;
            if (index < count)
            {
                DataSet ds = FillDataset(sql);
                DataRow row = ds.Tables[0].Rows[index];
                get_Tabdanhsachgv(sql, row);
                index = ds.Tables[0].Rows.IndexOf(row);
            }
            else
            {
                index--;
                MessageBox.Show("Bạn đang ở mẫu tin cuối cùng");
            }
        }

        private void btnVecuoi_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[count - 1];
            get_Tabdanhsachgv(sql, row);
            index = ds.Tables[0].Rows.IndexOf(row);
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            btnVedau.Enabled = false;
            btnVecuoi.Enabled = false;
            btnLui.Enabled = false;
            btnToi.Enabled = false;
            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnThoat.Enabled = false;
            btnLuu.Enabled = true;
            btnBoqua.Enabled = true;
            temp = false;
            txtMSGV.Text = "";
            txtHolot.Text = "";
            lkuMakhoa.EditValue = "";
            txtNoiSinh.Text = "";
            dNgaysinh.EditValue = "";
            txtDiachi.Text = "";
            txtDienthoai.Text = "";
            cbChucdanh.Text = "";
            cbLoaiGV.Text = "";
            lkuMaTD.EditValue = "";
            mGhichu.Text = "";
            txtSoCMND.Text = "";
            enable_textbox();
        }

        public void query(string command)
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
            connection.Open();
            SqlCommand sqlCommand = connection.CreateCommand();
            sqlCommand.CommandText = command;
            SqlDataReader dr = sqlCommand.ExecuteReader();
            connection.Close();
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có chắc muốn xóa không?.", "Quản lý sinh viên", MessageBoxButtons.OKCancel, MessageBoxIcon.None) == DialogResult.OK)
            {
                try
                {
                    xoa = true;
                    string str = "delete from T02_Giao_Vien where MagvID=N'" + txtMSGV.Text + "' ";
                    query(str);
                    get_gridviewdanhsachgv();
                    btnLui_Click(sender, e);
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error: " + err.Message);
                }
            }
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            temp = true;
            btnVedau.Enabled = false;
            btnVecuoi.Enabled = false;
            btnLui.Enabled = false;
            btnToi.Enabled = false;
            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnThoat.Enabled = false;
            btnLuu.Enabled = true;
            btnBoqua.Enabled = true;
            enable_textbox();
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            bool ktra = dxValidationProvider1.Validate();
            string shin;
            if (ckDacogd.Checked)
                shin = "true";
            else
                shin = "false";
            if (ktra)
            {
                if (temp == false)
                {
                    // lấy mã ID của GV ra
                    string sql = "select Top 1 gv.ID from T02_Giao_vien gv,T06_Trinh_do td, T16_Khoa kh where kh.Makhoa = gv.MaKhoa and td.MatrinhdoID = gv.MatrinhdoID Order by gv.MagvID DESC";
                    DataSet ds = FillDataset(sql);
                    DataRow row = ds.Tables[0].Rows[0];
                    int a = Int32.Parse(row["ID"].ToString());
                    string idgv = null;
                    //a++;
                    if (a < 10)
                    {
                        idgv = "GV00" + a.ToString();
                    }
                    if (a > 10 && a < 100)
                    {
                        idgv = "GV0" + a.ToString();
                    }
                    string str = "insert into T02_Giao_vien (MagvID, Hoten, MaKhoa, NgaySinh, NoiSinh, Phai, SoCMND, Diachi, Dienthoai, MatrinhdoID, HocHam, LoaiGV, TinhtrangGD,Ghichu, Ngayvetruong) values (N'" + idgv + "', N'" + txtHolot.Text + "', N'" + makhoa + "', N'" + dNgaysinh.EditValue + "', N'" + txtNoiSinh.Text + "', N'" + phai + "',N'" + txtSoCMND.Text + "',  N'" + txtDiachi.Text + "', N'" + txtDienthoai.Text + "', N'" + matrinhdo + "', N'" + cbChucdanh.Text + "', N'" + cbLoaiGV.Text + "', N'" + shin + "', N'" + mGhichu.Text + "', N'" + dNgayvetruong.EditValue + "')";
                    query(str);
                }
                else
                {
                    reset_lookupedit();
                    string str = "update T02_Giao_vien set Hoten = N'" + txtHolot.Text + "', MaKhoa = N'" + lkuMakhoa.Text + "', NgaySinh = N'" + dNgaysinh.EditValue + "', NoiSinh = N'" + txtNoiSinh.Text + "', Phai= N'" + phai + "', SoCMND= N'" + txtSoCMND.Text + "', Diachi= N'" + txtDiachi.Text + "', Dienthoai= N'" + txtDienthoai.Text + "', MatrinhdoID= N'" + lkuMaTD.Text + "', HocHam= N'" + cbChucdanh.Text + "', LoaiGV= N'" + cbLoaiGV.Text + "', TinhtrangGD= N'" + shin + "',Ghichu= N'" + mGhichu.Text + "', Ngayvetruong= N'" + dNgayvetruong.EditValue + "' where MagvID = N'" + txtMSGV.Text + "'";
                    query(str);
                }
                disable_textbox();
                btnVedau.Enabled = true;
                btnVecuoi.Enabled = true;
                btnLui.Enabled = true;
                btnToi.Enabled = true;
                btnThem.Enabled = true;
                btnSua.Enabled = true;
                btnXoa.Enabled = true;
                btnThoat.Enabled = true;
                btnLuu.Enabled = false;
                btnBoqua.Enabled = false;
                get_gridviewdanhsachgv();
            }
            else
                MessageBox.Show("Bạn chưa nhập dữ liệu cần thiết", "Quản lý sinh viên", MessageBoxButtons.OK, MessageBoxIcon.None);
        }

        private void btnBoqua_Click(object sender, EventArgs e)
        {
            btnVedau.Enabled = true;
            btnVecuoi.Enabled = true;
            btnLui.Enabled = true;
            btnToi.Enabled = true;
            btnThem.Enabled = true;
            btnSua.Enabled = true;
            btnXoa.Enabled = true;
            btnThoat.Enabled = true;
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[index];
            get_Tabdanhsachgv(sql, row);
            disable_textbox();
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void lkuMakhoa_EditValueChanged(object sender, EventArgs e)
        {
            makhoa = lkuMakhoa.EditValue.ToString();
        }

        private void lkuMaTD_EditValueChanged(object sender, EventArgs e)
        {
            matrinhdo = lkuMaTD.EditValue.ToString();
        }

        //get giá trị của phái
        private void rgPhai_SelectedIndexChanged(object sender, EventArgs e)
        {
            int selectedIndex = rgPhai.SelectedIndex;
            if (selectedIndex == 0)
                phai = "true";
            else
                phai = "false";
        }

        private void cbThi_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        public void disable_textbox()
        {
            txtMSGV.Enabled = false;
            txtDiachi.Enabled = false;
            txtDienthoai.Enabled = false;
            txtHolot.Enabled = false;
            txtMSGV.Enabled = false;
            txtNoiSinh.Enabled = false;
            txtSoCMND.Enabled = false;
            lkuMakhoa.Enabled = false;
            lkuMaTD.Enabled = false;
            cbChucdanh.Enabled = false;
            cbLoaiGV.Enabled = false;
            mGhichu.Enabled = false;
            dNgaysinh.Enabled = false;
            dNgayvetruong.Enabled = false;
            rgPhai.Enabled = false;
            ckDacogd.Enabled = false;
        }

        public void enable_textbox()
        {
            txtDiachi.Enabled = true;
            txtDienthoai.Enabled = true;
            txtHolot.Enabled = true;
            //txtMSGV.Enabled = true;
            txtNoiSinh.Enabled = true;
            txtSoCMND.Enabled = true;
            lkuMakhoa.Enabled = true;
            lkuMaTD.Enabled = true;
            cbChucdanh.Enabled = true;
            cbLoaiGV.Enabled = true;
            mGhichu.Enabled = true;
            dNgaysinh.Enabled = true;
            dNgayvetruong.Enabled = true;
            rgPhai.Enabled = true;
            ckDacogd.Enabled = true;
        }


        //bắt sự kiện click current row trên gridview
        private void gridView1_RowClick(object sender, DevExpress.XtraGrid.Views.Grid.RowClickEventArgs e)
        {
            string MagvID = gridView1.GetRowCellValue(e.RowHandle, gCol_MaGV).ToString();
            string str = "select * from T02_Giao_vien gv,T06_Trinh_do td, T16_Khoa kh where kh.Makhoa = gv.MaKhoa and td.MatrinhdoID = gv.MatrinhdoID and gv.MagvID = N'" + MagvID + "'";
            DataSet ds = FillDataset(str);
            DataRow row = ds.Tables[0].Rows[0];
            int[] i = gridView1.GetSelectedRows();
            index = i[0];
            get_Tabdanhsachgv(str, row);
        }
    }
}
